Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Bind, Define, and Describe Functions

This section describes the bind, define, and describe functions.

Table 15-4 Bind, Define, and Describe Functions

Function | Purpose
--- | ---
OCIBindArrayOfStruct() | Set skip parameters for static array bind
OCIBindByName() | Bind by name
OCIBindByPos() | Bind by position
OCIBindDynamic() | Sets additional attributes after bind with `OCI_DATA_AT_EXEC` mode
OCIBindObject() | Set additional attributes for bind of named datatype
OCIDefineArrayOfStruct() | Set additional attributes for static array define
OCIDefineByPos() | Define an output variable association
OCIDefineDynamic() | Sets additional attributes for define in `OCI_DYNAMIC_FETCH` mode
OCIDefineObject() | Set additional attributes for define of named datatype
OCIDescribeAny() | Describe existing schema objects
OCIStmtGetBindInfo() | Get bind and indicator variable names and handle

## OCIBindArrayOfStruct()

Purpose

This call sets up the skip parameters for a static array bind.

Syntax

    sword OCIBindArrayOfStruct ( OCIBind     *bindp,
                                 OCIError    *errhp,
                                 ub4         pvskip,
                                 ub4         indskip,
                                 ub4         alskip,
                                 ub4         rcskip );

Parameters

bindp (IN/OUT)

  The handle to a bind structure.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

pvskip (IN)

  Skip parameter for the next data value.

indskip (IN)

  Skip parameter for the next indicator value or structure.

alskip (IN)

  Skip parameter for the next actual length value.

rcskip (IN)

  Skip parameter for the next column-level return code value.

Comments

This call sets up the skip parameters necessary for a static array bind. It follows a call to `OCIBindByName()` or `OCIBindByPos()`. The bind handle returned by that initial bind call is used as a parameter for the `OCIBindArrayOfStruct()` call.

See Also: For information about skip parameters, "Binding and Defining Arrays of Structures in OCI".

Related Functions

OCIBindByName(), OCIBindByPos()

## OCIBindByName()

Purpose

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block.

Syntax

    sword OCIBindByName ( OCIStmt       *stmtp,
                          OCIBind       **bindpp,
                          OCIError      *errhp,
                          CONST text    *placeholder,
                          sb4           placeh_len,
                          dvoid         *valuep,
                          sb4           value_sz,
                          ub2           dty,
                          dvoid         *indp,
                          ub2           *alenp,
                          ub2           *rcodep,
                          ub4           maxarr_len,
                          ub4           *curelep,
                          ub4           mode );

Parameters

stmtp (IN/OUT)

  The statement handle to the SQL or PL/SQL statement being processed.

bindpp (IN/OUT)

  A pointer to save the pointer of a bind handle which is implicitly allocated by this call. The bind handle maintains all the bind information for this particular input value. The default encoding for the call depends on the UTF-16 setting in `stmtp` unless the `mode` parameter has a different value. The handle is freed implicitly when the statement handle is deallocated. On input, the value of the pointer must be `NULL` or a valid bind handle.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

placeholder (IN)

  The placeholder, specified by its name, which maps to a variable in the statement associated with the statement handle. The encoding of `placeholder` should always be consistent with that of the environment. That is, if the statement is prepared in UTF-16, so is the placeholder. As a string type parameter, it should be cast as `(text *)` and terminated with `NULL`.

placeh_len (IN)

  The length of the name specified in placeholder, in number of bytes regardless of the encoding.

valuep (IN/OUT)

  The pointer to a data value or an array of data values of the type specified in the `dty` parameter. This data could be a UTF-16 (formerly known as UCS-2) string, if an `OCIAttrSet()` function has been called to set `OCI_ATTR_CHARSET_ID` as `OCI_UTF16ID` or the deprecated `OCI_UCS2ID`. `OCI_UTF16ID` is the new designation for `OCI_UCS2ID`.

  Furthermore, as pointed out for `OCIStmtPrepare()`, the default encoding for the string type `valuep` will be in the encoding specified by the `charset` parameter of a previous call to `OCIEnvNlsCreate()`, unless users call `OCIAttrSet()` to manually reset the character set for the bind handle.

  See Also: "Bind Handle Attributes".

An array of data values can be specified for mapping into a PL/SQL table or for providing data for SQL multiple-row operations. When an array of bind values is provided, this is called an array bind in OCI terms.

For SQLT_NTY or SQLT_REF binds, the `valuep` parameter is ignored. The pointers to OUT buffers are set in the pgvpp parameter initialized by `OCIBindObject()`.

value_sz(IN)

  The size in bytes of the data value pointed to by dvoid pointer `valuep`. Although the bind buffer valuep could be of string type, the length is measured in number of bytes because the pointer passed down is of `(dvoid *)` type. In the case of an array bind, this is the maximum size of any element possible with the actual sizes being specified in the `alenp` parameter.

  For descriptors, locators, or `REF`s, whose size is unknown to client applications use the size of the structure you are passing in; `sizeof(OCILobLocator *)`.

dty (IN)

  The datatype of the value(s) being bound. Named datatypes (SQLT_NTY) and `REF`s (SQLT_REF) are valid only if the application has been initialized in object mode. For named datatypes, or `REF`s, additional calls must be made with the bind handle to set up the datatype-specific attributes.

indp (IN/OUT)

  Pointer to an indicator variable or array. For all datatypes except SQLT_NTY, this is a pointer to sb2 or an array of sb2s.

  For SQLT_NTY, this pointer is ignored and the actual pointer to the indicator structure or an array of indicator structures is initialized in a subsequent call to `OCIBindObject()`. This parameter is ignored for dynamic binds.

  See Also: "Indicator Variables"

alenp (IN/OUT)

  Pointer to array of actual lengths of array elements. Each element in `alenp` is the length of the data in the corresponding element in the bind value array before and after the execute. The length should be in bytes for strings passed in as a text type. This parameter is ignored for dynamic binds.

rcodep (OUT)

  Pointer to array of column level return codes. This parameter is ignored for dynamic binds.

maxarr_len (IN)

  The maximum possible number of elements of type `dty` in a PL/SQL binds. This parameter is not required for non-PL/SQL binds. If `maxarr_len` is nonzero, then either `OCIBindDynamic()` or `OCIBindArrayOfStruct()` can be invoked to set up additional bind attributes.

curelep (IN/OUT)

  A pointer to the actual number of elements. This parameter is only required for PL/SQL binds.

mode (IN)

  To maintain coding consistency, theoretically, this parameter can take all three possible values used by `OCIStmtPrepare()`. Since the encoding of bind variables should always be same as that of the statement containing this variable, an error will be raised if the user specify an encoding other than that of the statement. So the recommended setting for mode is `OCI_DEFAULT`, which will make the bind variable have the same encoding as its statement.

  The valid modes are:

- `OCI_DEFAULT` - The default mode. The statement handle `stmtp` uses whatever is specified by its parent environment handle.

- `OCI_BIND_SOFT` - Soft bind mode. This mode increases the performance of the call. If this is the first bind or some input value like `dty` or `value_sz` is changed from the previous bind, this mode is ignored. An error is returned if the statement is not executed. Unexpected behavior results if the bind handle passed is not valid.

- `OCI_DATA_AT_EXEC` - When this mode is selected, the `value_sz` parameter defines the maximum size of the data that can ever be provided at runtime. The application must be ready to provide the OCI library runtime IN data buffers at any time and any number of times. Runtime data is provided in one of these two ways:

  - Callbacks using a user-defined function which must be registered with a subsequent call to `OCIBindDynamic()`.

  - A polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are defined.

    See Also: For more information about using the `OCI_DATA_AT_EXEC` mode, "Runtime Data Allocation and Piecewise Operations in OCI".

  When mode is set to `OCI_DATA_AT_EXEC`, do not provide values for `valuep`, `indp`, `alenp`, and `rcodep` in the main call. Pass zeroes for `indp` and `alenp`. Provide the values through the callback function registered using `OCIBindDynamic()`.

  When the allocated buffers are not required any more, they should be freed by the client.

Comments

This call is used to perform a basic bind operation. The bind creates an association between the address of a program variable and a placeholder in a SQL statement or PL/SQL block. The bind call also specifies the type of data which is being bound, and may also indicate the method by which data will be provided at runtime.

Encoding is determined by either the bind handle using the setting in the statement handle as default, or you can override the setting by specifying the `mode` parameter explicitly.

Note: After using `OCIEnvNlsCreate()` to create the environment handle, the actual lengths and returned lengths of bind and define handles are always in number of bytes.

This function also implicitly allocates the bind handle indicated by the `bindpp` parameter. If a non-`NULL` pointer is passed in `**bindpp`, the OCI assumes that this points to a valid handle that has been previously allocated with a call to `OCIHandleAlloc()` or `OCIBindByName()`.

Data in an OCI application can be bound to placeholders statically or dynamically. Binding is static when all the IN bind data and the OUT bind buffers are well-defined just before the execute. Binding is dynamic when the IN bind data and the OUT bind buffers are provided by the application on demand at execute time to the client library. Dynamic binding is indicated by setting the `mode` parameter of this call to `OCI_DATA_AT_EXEC`.

See Also: For more information about dynamic binding, "Runtime Data Allocation and Piecewise Operations in OCI"

Both `OCIBindByName()` and `OCIBindByPos()` take as a parameter a bind handle, which is implicitly allocated by the bind call A separate bind handle is allocated for each placeholder the application is binding.

Additional bind calls may be required to specify particular attributes necessary when binding certain datatypes or handling input data in certain ways:

- If arrays of structures are being utilized, OCIBindArrayOfStruct() must be called to set up the necessary skip parameters.

- If data is being provided dynamically at runtime, and the application will be using user-defined callback functions, OCIBindDynamic() must be called to register the callbacks.

- If lengths in `alenp` greater than 64 Kbytes are required, use `OCIBindDynamic()`.

- If a named datatype is being bound, OCIBindObject() must be called to specify additional necessary information.

- If a statement with RETURNING clause is used, a call to `OCIBindDynamic()` must follow this call.

Related Functions

OCIBindDynamic(), OCIBindObject(), OCIBindArrayOfStruct()

## OCIBindByPos()

Purpose

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block.

Syntax

    sword OCIBindByPos ( OCIStmt      *stmtp,
                         OCIBind      **bindpp,
                         OCIError     *errhp,
                         ub4          position,
                         dvoid        *valuep,
                         sb4          value_sz,
                         ub2          dty,
                         dvoid        *indp,
                         ub2          *alenp,
                         ub2          *rcodep,
                         ub4          maxarr_len,
                         ub4          *curelep,
                         ub4          mode );

Parameters

stmtp (IN/OUT)

  The statement handle to the SQL or PL/SQL statement being processed.

bindpp (IN/OUT)

  An address of a bind handle which is implicitly allocated by this call. The bind handle maintains all the bind information for this particular input value. The handle is freed implicitly when the statement handle is deallocated. On input, the value of the pointer must be `NULL` or a valid bind handle.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

position (IN)

  The placeholder attributes are specified by position if `OCIBindByPos()` is being called.

valuep (IN/OUT)

  An address of a data value or an array of data values of the type specified in the `dty` parameter. An array of data values can be specified for mapping into a PL/SQL table or for providing data for SQL multiple-row operations. When an array of bind values is provided, this is called an array bind in OCI terms.

  For a LOB, the buffer pointer must be a pointer to a LOB locator of type `OCILobLocator`. Give the address of the pointer.

  For SQLT_NTY or SQLT_REF binds, the `valuep` parameter is ignored. The pointers to OUT buffers are set in the `pgvpp` parameter initialized by `OCIBindObject()`.

  If the `OCI_ATTR_CHARSET_ID` attribute is set to `OCI_UTF16ID` (replaces the deprecated `OCI_UCS2ID`, which is retained for backward compatibility), all data passed to and received with the corresponding bind call is assumed to be in UTF-16 encoding.

  See Also: Bind Handle Attributes.

value_sz (IN)

  The size of a data value. In the case of an array bind, this is the maximum size of any element possible with the actual sizes being specified in the `alenp` parameter.

  For descriptors, locators, or `REF`s, whose size is unknown to client applications, use the size of the structure you are passing: for example, `sizeof` (`OCILobLocator *`).

  For a PL/SQL block, a `value_sz` greater than the width of a `CHAR` column will cause an error, because of how PL/SQL processes the `CHAR` dataype.

dty (IN)

  The datatype of the value(s) being bound. Named datatypes (SQLT_NTY) and `REF`s (SQLT_REF) are valid only if the application has been initialized in object mode. For named datatypes, or `REF`s, additional calls must be made with the bind handle to set up the datatype-specific attributes.

indp (IN/OUT)

  Pointer to an indicator variable or array. For all datatypes, this is a pointer to `sb2` or an array of `sb2` values. The only exception is SQLT_NTY, when this pointer is ignored and the actual pointer to the indicator structure or an array of indicator structures is initialized by `OCIBindObject()`. `indp` is ignored for dynamic binds. If `valuep` is an `OUT` parameter, then you must set `indp` to point to `OCI_IND_NULL`.

  See Also: "Indicator Variables"

alenp (IN/OUT)

  Pointer to array of actual lengths of array elements. Each element in `alenp` is the length (in bytes, unless the data in `valuep` is in Unicode, when it is in codepoints) of the data in the corresponding element in the bind value array before and after the execute. This parameter is ignored for dynamic binds. If `valuep` is an `OUT` parameter, then you must set `alenp` to point to 0.

  Note: If `alenp` is greater than `value_sz`, data will be skipped.

rcodep (OUT)

  Pointer to an array of column level return codes. This parameter is ignored for dynamic binds.

maxarr_len (IN)

  The maximum possible number of elements of type `dty` in a PL/SQL binds. This parameter is not required for non-PL/SQL binds. If `maxarr_len` is nonzero, then either `OCIBindDynamic()` or `OCIBindArrayOfStruct()` can be invoked to set up additional bind attributes.

curelep (IN/OUT)

  A pointer to the actual number of elements. This parameter is only required for PL/SQL binds.

mode (IN)

  The valid modes for this parameter are:

  - `OCI_DEFAULT` - This is default mode.

  - `OCI_BIND_SOFT` - Soft bind mode. This mode increases the performance of the call. If this is the first bind or some input value like `dty` or `value_sz` is changed from the previous bind, this mode is ignored. An error is returned if the statement is not executed. Unexpected behavior results if the bind handle passed is not valid.

  - `OCI_DATA_AT_EXEC` - When this mode is selected, the `value_sz` parameter defines the maximum size of the data that can ever be provided at runtime. The application must be ready to provide the OCI library runtime IN data buffers at any time and any number of times. Runtime data is provided in one of the two ways:

    - Callbacks using a user-defined function which must be registered with a subsequent call to `OCIBindDynamic()`.

    - A polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are define.

      See Also: For more information about using the `OCI_DATA_AT_EXEC` mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

    When mode is set to `OCI_DATA_AT_EXEC`, do not provide values for `valuep`, `indp`, `alenp`, and `rcodep` in the main call. Pass zeroes for `indp` and `alenp`. Provide the values through the callback function registered using `OCIBindDynamic()`.

    When the allocated buffers are not required any more, they should be freed by the client.

Comments

This call is used to perform a basic bind operation. The bind creates an association between the address of a program variable and a placeholder in a SQL statement or PL/SQL block. The bind call also specifies the type of data which is being bound, and may also indicate the method by which data will be provided at runtime.

Note: After using `OCIEnvNlsCreate()` to create the environment handle, the actual lengths and returned lengths of bind and define handles are always in number of bytes.

This function also implicitly allocates the bind handle indicated by the `bindpp` parameter. If a non-`NULL` pointer is passed in `**bindpp`, the OCI assumes that this points to a valid handle that has been previously allocated with a call to `OCIHandleAlloc()` or `OCIBindByPos()`.

Data in an OCI application can be bound to placeholders statically or dynamically. Binding is static when all the IN bind data and the OUT bind buffers are well-defined just before the execute. Binding is dynamic when the IN bind data and the OUT bind buffers are provided by the application on demand at execute time to the client library. Dynamic binding is indicated by setting the `mode` parameter of this call to `OCI_DATA_AT_EXEC`.

See Also: For more information about dynamic binding, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

Both `OCIBindByName()` and `OCIBindByPos()` take as a parameter a bind handle, which is implicitly allocated by the bind call A separate bind handle is allocated for each placeholder the application is binding.

Additional bind calls may be required to specify particular attributes necessary when binding certain datatypes or handling input data in certain ways:

- If arrays of structures are being utilized, OCIBindArrayOfStruct() must be called to set up the necessary skip parameters.

- If data is being provided dynamically at runtime, and the application will be using user-defined callback functions, OCIBindDynamic() must be called to register the callbacks.

- If lengths in `alenp` greater than 64Kbytes are required, use `OCIBindDynamic()`.

- If a named datatype is being bound, OCIBindObject() must be called to specify additional necessary information.

- If a statement with RETURNING clause is used, a call to `OCIBindDynamic()` must follow this call.

Related Functions

OCIBindDynamic(), OCIBindObject(), OCIBindArrayOfStruct()

## OCIBindDynamic()

Purpose

This call is used to register user callbacks for dynamic data allocation.

Syntax

    sword OCIBindDynamic ( OCIBind     *bindp,
                           OCIError    *errhp,
                           dvoid       *ictxp,
                           OCICallbackInBind         (icbfp)(/*_
                                    dvoid            *ictxp,
                                    OCIBind          *bindp,
                                    ub4              iter,
                                    ub4              index,
                                    dvoid            **bufpp,
                                    ub4              *alenp,
                                    ub1              *piecep,
                                    dvoid            **indpp */),
                                    dvoid            *octxp,
                           OCICallbackOutBind        (ocbfp)(/*_
                                    dvoid            *octxp,
                                    OCIBind          *bindp,
                                    ub4              iter,
                                    ub4              index,
                                    dvoid            **bufpp,
                                    ub4              **alenpp,
                                    ub1              *piecep,
                                    dvoid            **indpp,
                                    ub2              **rcodepp _*/)   );

Parameters

bindp (IN/OUT)

  A bind handle returned by a call to `OCIBindByName()` or `OCIBindByPos()`.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

ictxp (IN)

  The context pointer required by the call back function `icbfp`.

icbfp (IN)

  The callback function which returns a pointer to the IN bind value or piece at run time. The callback takes in the following parameters:

ictxp (IN/OUT)

  The context pointer for this callback function.

bindp (IN)

  The bind handle passed in to uniquely identify this bind variable.

iter (IN)

  0-based execute iteration value.

index (IN)

  Index of the current array, for an array bind in PL/SQL. For SQL it is the row index. The value is 0-based and not greater than

  ™p parameter of the bind call.

bufpp (OUT)

  The pointer to the buffer or storage. For descriptors, `*bufpp` contains a pointer to the descriptor. For example if you define

      OCILobLocator    *lobp;

  then you set `*bufpp` to `lobp`, not `*lobp`.

  For `REF`s, pass the address of the ref; that is, pass `&my_ref` for `*bufpp`.

  If the `OCI_ATTR_CHARSET_ID` attribute is set to `OCI_UTF16ID` (replaces the deprecated `OCI_UCS2ID`, which is retained for backward compatibility), all data passed to and received with the corresponding bind call is assumed to be in UTF-16 encoding.

  See Also: "Bind Handle Attributes".

alenp (OUT)

  A pointer to a storage for OCI to fill in the size of the bind value/piece after it has been read. For descriptors, pass the size of the pointer to the descriptor; for example, `sizeof(OCILobLocator *)`.

piecep (OUT)

  Which piece of the bind value. This can be one of the following values `OCI_ONE_PIECE`, `OCI_FIRST_PIECE`, `OCI_NEXT_PIECE` and `OCI_LAST_PIECE`. For datatypes that do not support piecewise operations, you must pass `OCI_ONE_PIECE` or an error will be generated.

indp (OUT)

  Contains the indicator value. This is a pointer to either an sb2 value or a pointer to an indicator structure for binding named datatypes.

octxp (IN)

  The context pointer required by the callback function `ocbfp`.

ocbfp (IN)

  The callback function which returns a pointer to the OUT bind value or piece at run time. The callback takes in the following parameters:

octxp (IN/OUT)

  The context pointer for this call back function.

bindp (IN)

  The bind handle passed in to uniquely identify this bind variable.

iter (IN)

  0-based execute iteration value.

index (IN)

  For PL/SQL index of the current array, for an array bind. For SQL, the index is the row number in the current iteration. It is 0-based, and must not be greater than `curelep` parameter of the bind call.

bufpp (OUT)

  A pointer to a buffer to write the bind value/piece.

  If the `OCI_ATTR_CHARSET_ID` attribute is set to `OCI_UTF16ID` (replaces the deprecated `OCI_UCS2ID`, which is retained for backward compatibility), all data passed to and received with the corresponding bind call is assumed to be in UTF-16 encoding. For more information, refer to "Bind Handle Attributes".

alenpp (IN/OUT)

  A pointer to a storage for OCI to fill in the size of the bind value/piece after it has been read. It is in bytes except for Unicode encoding (if the `OCI_ATTR_CHARSET_ID` attribute is set to `OCI_UTF16ID`), when it is in codepoints.

piecep (IN/OUT)

  Returns a piece value from the callback (application) to Oracle, as follows:

  - `IN` - The value can be `OCI_ONE_PIECE` or `OCI_NEXT_PIECE`.

  - `OUT` - Depends on the IN value:

  If IN value is `OCI_ONE_PIECE`, then OUT value can be `OCI_ONE_PIECE` or `OCI_FIRST_PIECE`

  If IN value is `OCI_NEXT_PIECE` then OUT value can be `OCI_NEXT_PIECE` or `OCI_LAST_PIECE`.

indpp (OUT)

  Returns a pointer to contain the indicator value which either an sb2 value or a pointer to an indicator structure for named datatypes.

rcodepp (OUT)

  Returns a pointer to contains the return code.

Comments

This call is used to register user-defined callback functions for providing or receiving data if `OCI_DATA_AT_EXEC` mode was specified in a previous call to `OCIBindByName()` or `OCIBindByPos()`.

The callback function pointers must return `OCI_CONTINUE` if it the call is successful. Any return code other than `OCI_CONTINUE` signals that the client wishes to terminate processing immediately.

See Also: For more information about the `OCI_DATA_AT_EXEC` mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

When passing the address of a storage area, make sure that the storage area will exist even after the application returns from the callback. This means that you should not allocate such storage on the stack.

Note: After using `OCIEnvNlsCreate()` to create the environment handle, the actual lengths and returned lengths of bind and define handles are always in number of bytes.

Related Functions

OCIBindByName(), OCIBindByPos()

## OCIBindObject()

Purpose

This function sets up additional attributes which are required for a named datatype (object) bind.

Syntax

    sword OCIBindObject ( OCIBind          *bindp,
                          OCIError         *errhp,
                          CONST OCIType    *type,
                          dvoid            **pgvpp,
                          ub4              *pvszsp,
                          dvoid            **indpp,
                          ub4              *indszp, );

Parameters

bindp (IN/OUT)

  The bind handle returned by the call to `OCIBindByName()` or `OCIBindByPos()`.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

type (IN)

  Points to the TDO which describes the type of the program variable being bound. Retrieved by calling `OCITypeByName()`. Optional for `REF`s in SQL, but required for `REF`s in PL/SQL.

pgvpp (IN/OUT)

  Address of the program variable buffer. For an array, `pgvpp` points to an array of addresses. When the bind variable is also an OUT variable, the OUT Named Datatype value or `REF` is allocated in the Object Cache, and a `REF` is returned.

  `pgvpp` is ignored if the `OCI_DATA_AT_EXEC` mode is set. Then the Named Datatype buffers are requested at runtime. For static array binds, skip factors may be specified using the `OCIBindArrayOfStruct()` call. The skip factors are used to compute the address of the next pointer to the value, the indicator structure and their sizes.

pvszsp (OUT) [optional]

  Points to the size of the program variable. The size of the named datatype is not required on input. For an array, `pvszsp` is an array of `ub4`s. On return, for OUT bind variables, this points to size(s) of the Named Datatypes and `REF`s received. `pvszsp` is ignored if the `OCI_DATA_AT_EXEC` mode is set. Then the size of the buffer is taken at runtime.

indpp (IN/OUT) [optional]

  Address of the program variable buffer containing the parallel indicator structure. For an array, points to an array of pointers. When the bind variable is also an OUT bind variable, memory is allocated in the object cache, to store the OUT indicator values. At the end of the execute when all OUT values have been received, `indpp` points to the pointers to these newly allocated indicator structures. Required only for SQLT_NTY binds. `indpp` is ignored if the `OCI_DATA_AT_EXEC` mode is set. Then the indicator is requested at runtime.

indszp (IN/OUT)

  Points to the size of the IN indicator structure program variable. For an array, it is an array of `sb2`s. On return for OUT bind variables, this points to sizes of the received OUT indicator structures. `indszp` is ignored if the `OCI_DATA_AT_EXEC` mode is set. Then the indicator size is requested at runtime.

Comments

This function sets up additional attributes which binding a named datatype or a `REF`. An error will be returned if this function is called when the OCI environment has been initialized in non-object mode.

This call takes as a parameter a type descriptor object (TDO) of datatype `OCIType` for the named datatype being defined. The TDO can be retrieved with a call to `OCITypeByName()`.

If the `OCI_DATA_AT_EXEC` mode was specified in `OCIBindByName()` or `OCIBindByPos()`, the pointers to the IN buffers are obtained either using the callback `icbfp` registered in the `OCIBindDynamic()` call or by the `OCIStmtSetPieceInfo()` call.

The buffers are dynamically allocated for the OUT data. The pointers to these buffers are returned either by

- calling `ocbfp()` registered by the `OCIBindDynamic()`

- or, by setting the pointer to the buffer in the buffer passed in by `OCIStmtSetPieceInfo()` called when `OCIStmtExecute()` returned `OCI_NEED_DATA`.

The memory of these client library-allocated buffers must be freed when not in use anymore by using the `OCIObjectFree()` call.

Related Functions

OCIBindByName(), OCIBindByPos()

## OCIDefineArrayOfStruct()

Purpose

This call specifies additional attributes necessary for a static array define, used in an array of structures (multi-row, multi-column) fetch.

Syntax

    sword OCIDefineArrayOfStruct ( OCIDefine   *defnp,
                                   OCIError    *errhp,
                                   ub4         pvskip,
                                   ub4         indskip,
                                   ub4         rlskip,
                                   ub4         rcskip );

Parameters

defnp (IN/OUT)

  The handle to the define structure which was returned by a call to `OCIDefineByPos()`.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

pvskip (IN)

  Skip parameter for the next data value.

indskip (IN)

  Skip parameter for the next indicator location.

rlskip (IN)

  Skip parameter for the next return length value.

rcskip (IN)

  Skip parameter for the next return code.

Comments

This call follows a call to `OCIDefineByPos()`. If the application is binding an array of structures involving objects, it must call `OCIDefineObject()` first, and then call `OCIDefineArrayOfStruct()`.

See Also: "Skip Parameters".

Related Functions

OCIDefineByPos(), OCIDefineObject()

## OCIDefineByPos()

Purpose

Associates an item in a select-list with the type and output data buffer.

Syntax

    sword OCIDefineByPos ( OCIStmt     *stmtp,
                           OCIDefine   **defnpp,
                           OCIError    *errhp,
                           ub4         position,
                           dvoid       *valuep,
                           sb4         value_sz,
                           ub2         dty,
                           dvoid       *indp,
                           ub2         *rlenp,
                           ub2         *rcodep,
                           ub4         mode );

Parameters

stmtp (IN/OUT)

  A handle to the requested SQL query operation.

defnpp (IN/OUT)

  A pointer to a pointer to a define handle. If this parameter is passed as `NULL`, this call implicitly allocates the define handle. In the case of a redefine, a non-`NULL` handle can be passed in this parameter. This handle is used to store the define information for this column.

  Note: The user must keep track of this pointer. If a second call to `OCIDefineByPos()` is made for the same column position, there is no guarantee that the same pointer is returned.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

position (IN)

  The position of this value in the select list. Positions are 1-based and are numbered from left to right.

valuep (IN/OUT)

  A pointer to a buffer or an array of buffers of the type specified in the `dty` parameter. A number of buffers can be specified when results for more than one row are desired in a single fetch call.

  For a LOB, the buffer pointer must be a pointer to a LOB locator of type `OCILobLocator`. Give the address of the pointer.

value_sz (IN)

  The size of each `valuep` buffer in bytes. If the data is stored internally in VARCHAR2 format, the number of characters desired, if different from the buffer size in bytes, may be additionally specified by using `OCIAttrSet()`.

  In a multibyte conversion environment, a truncation error will be generated if the number of bytes specified is insufficient to handle the number of characters desired.

  If the `OCI_ATTR_CHARSET_ID` attribute is set to `OCI_UTF16ID` (replaces the deprecated `OCI_UCS2ID`, which is retained for backward compatibility), all data passed to and received with the corresponding define call is assumed to be in UTF-16 encoding.

  See Also: "Bind Handle Attributes"

dty (IN)

  The datatype. Named datatype (SQLT_NTY) and `REF` (SQLT_REF) are valid only if the environment has been initialized in object mode.

  SQLT_CHAR and SQLT_LNG can be specified for `CLOB` columns, and SQLT_BIN sand SQLT_LBI for `BLOB` columns.

  See Also: For a listing of datatype codes and values, refer to Chapter 3, "Datatypes"

indp (IN)

  pointer to an indicator variable or array. For scalar datatypes, pointer to `sb2` or an array of `sb2`s. Ignored for SQLT_NTY defines. For SQLT_NTY defines, a pointer to a named datatype indicator structure or an array of named datatype indicator structures is associated by a subsequent `OCIDefineObject()` call.

  See Also: "Indicator Variables"

rlenp (IN/OUT)

  Pointer to array of length of data fetched. Each element in `rlenp` is the length of the data (in bytes, unless the data in `valuep` is in Unicode, when it is in codepoints) in the corresponding element in the row after the fetch.

rcodep (OUT)

  Pointer to array of column-level return codes

mode (IN)

  The valid modes are:

- `OCI_DEFAULT` - This is the default mode.

- `OCI_DEFINE_SOFT` - Soft define mode. This mode increases the performance of the call. If this is the first define or some input parameter like `dty` or `value_sz` is changed from the previous define, this mode is ignored. Unexpected behavior results if a non-valid define handle is passed. An error is returned if the statement is not executed.

- `OCI_DYNAMIC_FETCH` - For applications requiring dynamically allocated data at the time of fetch, this mode must be used. You can define a callback using the `OCIDefineDynamic()` call. The `value_sz` parameter defines the maximum size of the data that will be provided at runtime. When the client library needs a buffer to return the fetched data, the callback will be invoked to provide a run-time buffer into which a piece or the whole data will be returned.

Comments

This call defines an output buffer which will receive data retrieved from Oracle. The define is a local step which is necessary when a `SELECT` statement returns data to your OCI application.

Note: After using `OCIEnvNlsCreate()` to create the environment handle, the actual lengths and returned lengths of bind and define handles are always in number of bytes.

This call also implicitly allocates the define handle for the select-list item. If a non-`NULL` pointer is passed in `*defnpp`, the OCI assumes that this points to a valid handle that has been previously allocated with a call to `OCIHandleAlloc()` or `OCIDefineByPos()`. This would be true in the case of an application which is redefining a handle to a different addresses so it can reuse the same define handle for multiple fetches.

Defining attributes of a column for a fetch is done in one or more calls. The first call is to `OCIDefineByPos()`, which defines the minimal attributes required to specify the fetch.

Following the call to `OCIDefineByPos()` additional define calls may be necessary for certain datatypes or fetch modes:

- A call to OCIDefineArrayOfStruct() is necessary to set up skip parameters for an array fetch of multiple columns.

- A call to OCIDefineObject() is necessary to set up the appropriate attributes of a named datatype (that is, object or collection) or `REF` fetch. In this case the data buffer pointer in `OCIDefineByPos()` is ignored.

- Both `OCIDefineArrayOfStruct()` and `OCIDefineObject()` must be called after `OCIDefineByPos()` in order to fetch multiple rows with a column of named datatypes.

For a LOB define, the buffer pointer must be a pointer to a LOB locator of type `OCILobLocator`, allocated by the `OCIDescriptorAlloc()` call. LOB locators, and not LOB values, are always returned for a LOB column. LOB values can then be fetched using OCI LOB calls on the fetched locator. This same mechanism is true for all descriptor datatypes.

For NCHAR (fixed and varying length), the buffer pointer must point to an array of bytes sufficient for holding the required NCHAR characters.

Nested table columns are defined and fetched like any other named datatype.

When defining an array of descriptors or locators, you should pass in an array of pointers to descriptors or locators.

When doing an array define for character columns, you should pass in an array of character buffers.

If the `mode` parameter is this call is set to `OCI_DYNAMIC_FETCH`, the client application can fetch data dynamically at runtime. Runtime data can be provided in one of two ways:

- callbacks using a user-defined function which must be registered with a subsequent call to `OCIDefineDynamic()`. When the client library needs a buffer to return the fetched data, the callback will be invoked and the runtime buffers provided will return a piece or the whole data.

  - a polling mechanism using calls supplied by the OCI. This mode is assumed if no callbacks are defined. In this case, the fetch call returns the `OCI_NEED_DATA` error code, and a piecewise polling method is used to provide the data.

    See Also: For more information about using the `OCI_DYNAMIC_FETCH` mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI". For more information about defines, see "Overview of Defining in OCI".

Related Functions

OCIDefineArrayOfStruct(), OCIDefineDynamic(), OCIDefineObject()

## OCIDefineDynamic()

Purpose

This call is used to set the additional attributes required if the `OCI_DYNAMIC_FETCH` mode was selected in `OCIDefineByPos()`.

Syntax

    sword OCIDefineDynamic ( OCIDefine   *defnp,
                             OCIError    *errhp,
                             dvoid       *octxp,
                             OCICallbackDefine       (ocbfp)(/*_
                                      dvoid          *octxp,
                                      OCIDefine      *defnp,
                                      ub4            iter,
                                      dvoid          **bufpp,
                                      ub4            **alenpp,
                                      ub1            *piecep,
                                      dvoid          **indpp,
                                      ub2            **rcodep _*/)  );

Parameters

defnp (IN/OUT)

  The handle to a define structure returned by a call to `OCIDefineByPos()`.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

octxp (IN)

  Points to a context for the callback function.

ocbfp (IN)

  Points to a callback function. This is invoked at runtime to get a pointer to the buffer into which the fetched data or a piece of it will be retrieved. The callback also specifies the indicator, the return code and the lengths of the data piece and indicator.

  Caution: When working with callback parameters, it is important to keep in mind what is meant by IN and OUT for the parameter mode. Normally, in an OCI function, an IN parameter refers to data being passed to Oracle, and an OUT parameter refers to data coming back from Oracle. In the case of callbacks, this is reversed. IN means data is coming from Oracle into the callback, and OUT means data is coming out of the callback and going to Oracle.

  The callback parameters are listed next:

octxp (IN/OUT)

  A context pointer passed as an argument to all the callback functions.

defnp (IN)

  The define handle.

iter (IN)

  Which row of this current fetch; 0-based.

bufpp (OUT)

  Returns a pointer to a buffer to store the column value, that is, `*bufpp` points to some appropriate storage for the column value.

alenpp (IN/OUT)

  Used by the application to set the size of the storage it is providing in `*bufpp`. After data is fetched into the buffer, `alenpp` indicates the actual size of the data in bytes.

piecep (IN/OUT)

  Returns a piece value from the callback (application) to Oracle, as follows:

  - `IN` - The value can be `OCI_ONE_PIECE` or `OCI_NEXT_PIECE`.

  - `OUT` - Depends on the IN value:

  If IN value is `OCI_ONE_PIECE`, then OUT value can be `OCI_ONE_PIECE` or `OCI_FIRST_PIECE`

  If IN value is `OCI_NEXT_PIECE` then OUT value can be `OCI_NEXT_PIECE` or `OCI_LAST_PIECE`

indpp (IN)

  Indicator variable pointer

rcodep (IN)

  Return code variable pointer

Comments

This call is used to set the additional attributes required if the `OCI_DYNAMIC_FETCH` mode has been selected in a call to `OCIDefineByPos()`. If `OCI_DYNAMIC_FETCH` mode was selected, and the call to `OCIDefineDynamic()` is skipped, then the application can fetch data piecewise using OCI calls (OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo()). For more information about `OCI_DYNAMIC_FETCH` mode, see the section "Runtime Data Allocation and Piecewise Operations in OCI".

Note: After using `OCIEnvNlsCreate()` to create the environment handle, the actual lengths and returned lengths of bind and define handles are always in number of bytes.

Related Functions

OCIDefineByPos()

See Also: Oracle Database Application Developer's Guide - Fundamentals

## OCIDefineObject()

Purpose

Sets up additional attributes necessary for a named datatype or `REF` define.

Syntax

    sword OCIDefineObject ( OCIDefine       *defnp,
                            OCIError        *errhp,
                            CONST OCIType   *type,
                            dvoid           **pgvpp,
                            ub4             *pvszsp,
                            dvoid           **indpp,
                            ub4             *indszp );

Parameters

defnp (IN/OUT)

  A define handle previously allocated in a call to `OCIDefineByPos()`.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

type (IN) [optional]

  Points to the Type Descriptor Object (TDO) which describes the type of the program variable. Only used for program variables of type SQLT_NTY. This parameter is optional, and may be passed as `NULL` if it is not being used.

pgvpp (IN/OUT)

  Points to a pointer to a program variable buffer. For an array, `pgvpp` points to an array of pointers. Memory for the fetched named datatype instance(s) is dynamically allocated in the object cache. At the end of the fetch when all the values have been received, `pgvpp` points to the pointer(s) to these newly allocated named datatype instance(s). The application must call `OCIObjectFree()` to deallocate the named datatype instance(s) when they are no longer needed.

  Note: If the application wants the buffer to be implicitly allocated in the cache, `*pgvpp` should be passed in as `NULL`.

pvszsp (IN/OUT)

  Points to the size of the program variable. For an array, it is an array of `ub4`s.

indpp (IN/OUT)

  Points to a pointer to the program variable buffer containing the parallel indicator structure. For an array, points to an array of pointers. Memory is allocated to store the indicator structures in the object cache. At the end of the fetch when all values have been received, `indpp` points to the pointer(s) to these newly allocated indicator structure(s).

indszp (IN/OUT)

  Points to the size(s) of the indicator structure program variable. For an array, it is an array of `ub4`s.

Comments

This function follows a call to `OCIDefineByPos()` to set initial define information. This call sets up additional attributes necessary for a Named Datatype define. An error will be returned if this function is called when the OCI environment has been initialized in non-Object mode.

This call takes as a parameter a type descriptor object (TDO) of datatype `OCIType` for the named datatype being defined. The TDO can be retrieved with a call to `OCIDescribeAny()`.

See Also: "OCIInitialize()" for more information about initializing the OCI process environment.

Related Functions

OCIDefineByPos()

## OCIDescribeAny()

Purpose

Describes existing schema and subschema objects.

Syntax

    sword OCIDescribeAny ( OCISvcCtx       *svchp,
                           OCIError        *errhp,
                           dvoid           *objptr,
                           ub4             objptr_len,
                           ub1             objptr_typ,
                           ub1             info_level,
                           ub1             objtyp,
                           OCIDescribe     *dschp );

Parameters

svchp (IN)

  A service context handle.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

objptr (IN)

  This parameter can be:

1. A string containing the name of the object to be described. Must be in the encoding specified by the `charset` parameter of a previous call to `OCIEnvNlsCreate()`.

2. A pointer to a `REF` to the TDO (for a type).

3. A pointer to a TDO (for a type).

These cases are distinguished by passing the appropriate value for `objptr_typ`. This parameter must be non-`NULL`.

In case 1, the string containing the object name should be in the format `name1[.name2 ...][@linkname]`, such as `hr.employees.employee_id@mydb`. Database links are only allowed to Oracle8i or later databases. The object name is interpreted by the following SQL rules:

- If only `name1` is entered and `objtyp` is equal to `OCI_PTYPE_SCHEMA`, then the name refers to the named schema. The Oracle database must be release 8.1 or later.

- If only `name1` is entered and `objtyp` is equal to `OCI_PTYPE_DATABASE`, then the name refers to the named database. When describing a remote database with `database_name@db_link_name`, the remote Oracle database must be release 8.1 or later.

- If only `name1` is entered and `objtyp` is not equal to `OCI_PTYPE_SCHEMA` or `OCI_PTYPE_DATABASE`, then the name refers to the named object (of type table, view, procedure, function, package, type, synonym, sequence) in the current schema of the current user. When connected to an Oracle7 Server, the only valid types are procedure and function.

- If `name1.name2.name3 ...` is entered, the object name refers to a schema or subschema object in the schema named `name1`. For example, in the string `scott.emp.deptno`, `scott` is the name of the schema, `emp` is the name of a table in the schema, and `deptno` is the name of a column in the table.

objnm_len (IN)

  The length of the name string pointed to by `objptr`. Must be nonzero if a name is passed. Can be zero if `objptr` is a pointer to a TDO or its `REF`.

objptr_typ (IN)

  The type of object passed in `objptr`. Valid values are:

- `OCI_OTYPE_NAME`, if `objptr` points to the name of a schema object

- `OCI_OTYPE_REF`, if `objptr` is a pointer to a `REF` to a TDO

- `OCI_OTYPE_PTR`, if `objptr` is a pointer to a TDO

info_level (IN)

  Reserved for future extensions. Pass `OCI_DEFAULT`.

objtyp (IN)

  The type of schema object being described. Valid values are:

- `OCI_PTYPE_TABLE`, for tables

- `OCI_PTYPE_VIEW`, for views

- `OCI_PTYPE_PROC`, for procedures

- `OCI_PTYPE_FUNC`, for functions

- `OCI_PTYPE_PKG`, for packages

- `OCI_PTYPE_TYPE`, for types

- `OCI_PTYPE_SYN`, for synonyms

- `OCI_PTYPE_SEQ`, for sequences

- `OCI_PTYPE_SCHEMA`, for schemas

- `OCI_PTYPE_DATABASE`, for databases

- `OCI_PTYPE_UNK`, for unknown schema objects

dschp (IN/OUT)

  A describe handle that is populated with describe information about the object after the call. Must be non-`NULL`.

Comments

This is a generic describe call that describes existing schema objects: tables, views, synonyms, procedures, functions, packages, sequences, types, schemas, and databases. This call also describes subschema objects, such as a column in a table. This call populates the describe handle with the object-specific attributes which can be obtained through an `OCIAttrGet()` call.

An `OCIParamGet()` on the describe handle returns a parameter descriptor for a specified position. Parameter positions begin with 1. Calling `OCIAttrGet()` on the parameter descriptor returns the specific attributes of a stored procedure or function parameter, or a table column descriptor. These subsequent calls do not need an extra round trip to the server because the entire schema object description is cached on the client side by `OCIDescribeAny()`. Calling `OCIAttrGet()` on the describe handle also returns the total number of positions.

If the `OCI_ATTR_DESC_PUBLIC` attribute is set on the describe handle, then the object named is looked up as a public synonym when the object does not exist in the current schema and only `name1` is specified.

See Also: For more information about describe operations, see Chapter 6, "Describing Schema Metadata"

Related Functions

OCIAttrGet(), OCIParamGet()

## OCIStmtGetBindInfo()

Purpose

Gets the bind and indicator variable names.

Syntax

    sword OCIStmtGetBindInfo ( OCIStmt      *stmtp,
                               OCIError     *errhp,
                               ub4          size,
                               ub4          startloc,
                               sb4          *found,
                               text         *bvnp[],
                               ub1          bvnl[],
                               text         *invp[],
                               ub1          inpl[],
                               ub1          dupl[],
                               OCIBind      *hndl[] );

Parameters

stmtp (IN)

  The statement handle prepared by `OCIStmtPrepare()`.

errhp (IN)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

size (IN)

  The number of elements in each array.

startloc (IN)

  Position of the bind variable at which to start getting bind information.

found (IN)

  `abs`(`found`) gives the total number of bind variables in the statement irrespective of the start position. Positive value if the number of bind variables returned is less than the size provided, otherwise negative.

bvnp (OUT)

  Array of pointers to hold bind variable names. Will be in the encoding specified by the `charset` parameter of a previous call to `OCIEnvNlsCreate()`.

bvnl (OUT)

  Array to hold the length of the each `bvnp` element. The length is in bytes.

invp (OUT)

  Array of pointers to hold indicator variable names. Must be in the encoding specified by the `charset` parameter of a previous call to `OCIEnvNlsCreate()`.

inpl (OUT)

  Array of pointers to hold the length of the each `invp` element. In number of bytes.

dupl (OUT)

  An array whose element value is 0 or 1 depending on whether the bind position is duplicate of another.

hndl (OUT)

  An array which returns the bind handle if binds have been done for the bind position. No handle is returned for duplicates.

Comments

This call returns information about bind variables after a statement has been prepared. This includes bind names, indicator names, and whether or not binds are duplicate binds. This call also returns an associated bind handle if there is one. The call sets the `found` parameter to the total number of bind variables and not just the number of distinct bind variables.

`OCI_NO_DATA` will be returned if the statement has no bind variables or if the starting bind position specified by the you in the invocation does not exist in the statement.

This function does not include `SELECT INTO` list variables, because they are not considered to be binds.

The statement must have been prepared with a call to `OCIStmtPrepare()` prior to this call. The encoding setting in the statement handle will determine whether Unicode strings will be retrieved.

This call is processed locally.

Related Functions

OCIStmtPrepare()